﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: March 2, 2016
-- Description:	Takes an EntityType[.Subtype] or a ShortRelationshipName (with or without direction) and 
--				returns query logic that should return an object with attributes as columns.
--				If a relationship, returns the word "REVERSE;" at the beginning of the query 
--				so the E1 and E2 can be switched.
-- =============================================
CREATE FUNCTION [dbo].[ConvertRafikiNOSQL_EntityOrRelSQL] 
(
	@EntityOrRelName nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
	DECLARE @EntityOrRel nvarchar(max)
	DECLARE @EntitySubType nvarchar(max)
	DECLARE @RafikiNoSQL nvarchar(max) = NULL
	DECLARE @QueryToReturn nvarchar(max)
	DECLARE @ShortRelationshipName nvarchar(20)
	-- Get Type or ShortRelationshipName
	IF CHARINDEX('.',@EntityOrRelName) <> 0
		SET @EntityOrRel = SUBSTRING(@EntityOrRelName,1,CHARINDEX('.',@EntityOrRelName)-1)
	ELSE 
		SET @EntityOrRel = @EntityOrRelName

	-- Get SubType or Relationship Direction
	-- If there is a . then there is a subtype or direction
	IF CHARINDEX('.',@EntityOrRelName) <> 0
		SET @EntitySubType = SUBSTRING(@EntityOrRelName,CHARINDEX('.',@EntityOrRelName)+1,LEN(@EntityOrRelName))
	ELSE 
		SET @EntitySubType = NULL

	SELECT @EntityOrRel = ShortRelationshipName,@RafikiNOSQL = RafikiNOSQL FROM DefinedEntityRelationships WHERE ShortRelationshipName = @EntityOrRel
	-- nonzero rowcount means that the specified item is a relationship
	IF @@ROWCOUNT <> 0 BEGIN
		IF NOT @RafikiNoSQL IS NULL BEGIN
			-- This is an external relationship
			SET @QueryToReturn = ''
							   + ' FROM (' + @RafikiNoSQL + ') ER ' 
							   + ' INNER JOIN DefinedEntityRelationships DER ON '
							   + ' DER.ShortRelationshipName = "' + @EntityOrRel + '"'
							   + ' WHERE ("@ED" = "" OR StartDate<="@ED" OR StartDate IS NULL) '
							   + ' AND ("@SD" = "" OR EndDate>="@SD" OR EndDate IS NULL) '

		END ELSE BEGIN
			SET @QueryToReturn = ''
							   + ' FROM EntityRelationships ER ' 
							   + ' INNER JOIN DefinedEntityRelationships DER ON '
							   + ' ER.ShortRelationshipName = DER.ShortRelationshipName AND '
							   + ' ER.ShortRelationshipName = ''' + @EntityOrRel + ''''
							   + ' INNER JOIN (SELECT EntityName AS Entity1Name,EntityID FROM Entities) E1 ON E1.EntityID = ER.Entity1ID '
							   + ' INNER JOIN (SELECT EntityName AS Entity2Name,EntityID FROM Entities) E2 ON E2.EntityID = ER.Entity2ID '
							   + ' WHERE ("@ED" = "" OR StartDate<="@ED" OR StartDate IS NULL) '
							   + ' AND ("@SD" = "" OR EndDate>="@SD" OR EndDate IS NULL) '

		END

		IF NOT @EntitySubType IS NULL BEGIN
			IF @EntitySubType = 'Forward'
				SET @QueryToReturn = 'SELECT CAST(ER.Entity1ID AS NVARCHAR(100)) AS Entity1ID,Entity1Name,DER.ShortRelationshipName,'
								   + ' CAST(ER.Entity2ID AS NVARCHAR(100)) AS Entity2ID,Entity2Name,DER.ShortRelationshipName + ''.Forward'' AS SRNameWithDir,ER.StartDate,ER.EndDate,ER.Notes, '
								   + ' DER.RelationshipName,DER.SuperRelationship '
								   + @QueryToReturn
			ELSE
				-- For reverse relationships, pass back the word reverse 
				SET @QueryToReturn = 'SELECT CAST(ER.Entity2ID AS NVARCHAR(100)) AS Entity1ID,Entity2Name AS Entity1Name,'
								   + ' DER.ShortRelationshipName,CAST(ER.Entity1ID AS NVARCHAR(100)) AS Entity2ID,Entity1Name AS Entity2Name,DER.ShortRelationshipName + ''.Reverse'' AS SRNameWithDir,'
								   + ' ER.StartDate,ER.EndDate,ER.Notes, '
								   + ' DER.ReverseRelationship AS RelationshipName,DER.SuperRelationship '
								   + @QueryToReturn
		END ELSE 
				SET @QueryToReturn = '(SELECT CAST(ER.Entity1ID AS NVARCHAR(100)) AS Entity1ID,Entity1Name,DER.ShortRelationshipName,'
								   + ' CAST(ER.Entity2ID AS NVARCHAR(100)) AS Entity2ID,Entity2Name,DER.ShortRelationshipName + ''.Forward'' AS SRNameWithDir,ER.StartDate,ER.EndDate,ER.Notes, '
								   + ' DER.RelationshipName,DER.SuperRelationship '
								   + @QueryToReturn + ') UNION ALL ('
								   + 'SELECT CAST(ER.Entity2ID AS NVARCHAR(100)) AS Entity1ID,Entity2Name AS Entity1Name,'
								   + ' DER.ShortRelationshipName,CAST(ER.Entity1ID AS NVARCHAR(100)) AS Entity2ID,Entity1Name AS Entity2Name,DER.ShortRelationshipName + ''.Reverse'' AS SRNameWithDir,'
								   + ' ER.StartDate,ER.EndDate,ER.Notes, '
								   + ' DER.ReverseRelationship AS RelationshipName,DER.SuperRelationship '
								   + @QueryToReturn + ')'

			--SET @QueryToReturn = 'SELECT CAST(ER.Entity1ID AS NVARCHAR(100)) AS Entity1ID,Entity1Name,DER.ShortRelationshipName,CAST(ER.Entity2ID AS NVARCHAR(100)) AS Entity2ID,Entity2Name,ER.StartDate,ER.EndDate,'
			--				   + ' ER.Notes,DER.RelationshipName,DER.SuperRelationship, '
			--				   + ' DER.ReverseRelationship '
			--				   + @QueryToReturn
		-- Before returning, union the query generated above with all sub relationships in bracket notation.
		SET @QueryToReturn = '(' + @QueryToReturn + ') '

		-- If the specified relationship is a super-relationship, expand to all sub-relationships. 
		-- (if not a super-relationship, this cursor will skip automatically)
		DECLARE @ExpansionCursor CURSOR
		SET @ExpansionCursor = CURSOR FAST_FORWARD
		FOR 
			SELECT ShortRelationshipname FROM DefinedEntityRelationships WHERE SuperRelationship = @EntityOrRel
		OPEN @ExpansionCursor
		FETCH NEXT FROM @ExpansionCursor
		INTO @ShortRelationshipName
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @QueryToReturn = @QueryToReturn + ' UNION ALL (SELECT * FROM {' + @ShortRelationshipName + COALESCE('.' + @EntitySubtype,'') + '}) '
	
			FETCH NEXT FROM @ExpansionCursor
			INTO @ShortRelationshipName
		END
		CLOSE @ExpansionCursor
		DEALLOCATE @ExpansionCursor
	END ELSE BEGIN
		-- zero rowcount means that the specified item probably is an entity type / subtype\
		SELECT @EntityOrRel = EntityType FROM DefinedEntityTypes WHERE EntityType = @EntityOrRel
		IF @@ROWCOUNT = 0
			RETURN 'Error: The item to be replaced is not in DefinedEntityTypes or DefinedEntityRelationships'

		-- If there is a subtype, check to see that it exists.
		IF NOT @EntitySubType IS NULL BEGIN
			SELECT @EntitySubType = EntitySubType FROM DefinedEntitySubTypes WHERE EntityType = @EntityOrRel AND EntitySubType = @EntitySubType
			IF @@ROWCOUNT = 0
				RETURN 'Error: The item to be replaced is not in DefinedEntitySubTypes or DefinedEntityRelationships'
		END

		-- EntityView turns the defined attributes into columns.
		SET @QueryToReturn = 'SELECT E.EntityType,E.EntitySubType,A.* FROM Entities AS E' 
								 --+ ' INNER JOIN #SecurityRelationships SR ON E.EntityID = SR.EntityID '
						   + ' INNER JOIN (' + dbo.ConvertRafikiNOSQL_EntityView(@EntityOrRel,@EntitySubType) + ') AS A'
						   + ' ON E.EntityID = A.EntityID AND E.EntityType = ''' + @EntityOrRel + ''''
		IF NOT @EntitySubType IS NULL
			SET @QueryToReturn = @QueryToReturn + ' AND E.EntitySubType = ''' 
								+ @EntitySubType + ''''
		ELSE
			SET @QueryToReturn = @QueryToReturn + ' AND E.EntitySubType IS NULL'
		-- The ReadDataFromDB_EntityView uses many, many, brackets and this will break the ConvertSQL
		-- function, so this step removes all brackets
		SET @QueryToReturn = REPLACE(@QueryToReturn,'[','')
		SET @QueryToReturn = REPLACE(@QueryToReturn,']','')
	END
	
	RETURN @QueryToReturn
END